package com.victor.core.util;

import com.victor.common.constant.Magic;
import com.victor.common.exception.ValidationException;
import org.apache.log4j.Logger;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.expression.EvaluationException;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 对execel文件的校验并且解析
 */
@SuppressWarnings("rawtypes")
public class ExcelUtil {
    Logger logger = Logger.getLogger(this.getClass());
    /**
     * 如果校验失败，将会给出详细提示信息
     */
    private String description = "";
    /**
     * execel 对象
     */
    private Sheet sheet;
    /**
     * 从xml读取到的execel表格信息
     */
    private List<String> fieldList;
    /**
     * 当前操作行
     */
    private int rowIndex = 0;
    /**
     * 每一行数据封装
     */
    private Object objectBean;
    /**
     * 数据的开始单元格
     */
    private Cell cellStart;
    /**
     * 需要封装的类
     */
    private Class clazz;
    /**
     * hibernate 的校验器
     */
    private Validator validator;
    /**
     * 从execel读到的某一行的数据
     */
    private String[] fieldVals;
    /**
     * 有效数据的列数
     */
    private int fieldSize = 0;
    /**
     * 针对日期的默认转换形式
     */
    private DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    /**
     * EL 解析器
     */
    private Expression exp;
    private ExpressionParser parser;

    @SuppressWarnings("unused")
    private DecimalFormat df = new DecimalFormat("#");

    public String getDescription() {
        return description;
    }

    public Object getObjectBean() {
        return objectBean;
    }

    /**
     * @param execelFilename execel文件名
     * @param xmlFilename    excel文件所对应的校验文件
     * @param calzz          需要封装的类
     * @throws IOException
     */
    public ExcelUtil(InputStream execelIS, String xmlFilename, Class clazz) throws ValidationException, IOException {
        /**
         * 打开execel工作簿
         */
        Workbook wb = null;
        /**
         * 区别excel2003和2007版本
         */
        if (!execelIS.markSupported()) {
            execelIS = new PushbackInputStream(execelIS, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(execelIS)) {
            wb = new HSSFWorkbook(execelIS);
        } else if (POIXMLDocument.hasOOXMLHeader(execelIS)) {
            wb = new XSSFWorkbook(execelIS);
        }
        /**
         * 默认取第一个工作簿
         */
        sheet = wb.getSheetAt(0);
        /**
         * 读配置文件，获取所有的属性列描述
         */
        fieldList = this.readFieldsFromXML(xmlFilename);
        /**
         * 个数
         */
        fieldSize = fieldList.size();

        /**
         * 找到有效数据的开始单元格
         */
        cellStart = this.findStartCell();
        if (cellStart == null) {
            throw new ValidationException("", this.description);
        }
        /**
         * 每次读取一行execel数据，rowIndex每次增1
         */
        rowIndex = cellStart.getRowIndex() + 1;

        /**
         * 需要封装的对象类
         */
        this.clazz = clazz;
        /**
         * 初始化校验器
         */
        ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        validator = factory.getValidator();
        /**
         * 初始化EL解析器
         */
        parser = new SpelExpressionParser();
        exp = parser.parseExpression("values");
    }

    /**
     * 是否还有数据
     *
     * @return
     */
    public boolean hasNext() {
        Row row = sheet.getRow(rowIndex++);
        if (row == null) {
            return false;
        }
        fieldVals = this.getRowValues(row, cellStart.getColumnIndex());
        if (Arrays.asList(fieldVals).indexOf("") != -1) {
            for (String s : fieldVals) {
                //如果每个字段都是空的，则返回false 否则true
                if (!"".equals(s)) {
                    return true;
                }
            }
            return false;
        }
        return true;
    }

    /**
     * 校验
     *
     * @return
     */
    public boolean validate() {
        try {
            objectBean = Class.forName(clazz.getName()).newInstance();
        } catch (Exception e) {
            logger.error(e);
        }

        try {
            /**
             * 给objectBean的属性赋值
             */
            exp.setValue(objectBean, fieldVals);

        } catch (EvaluationException e) {
            /**
             * 由于所有的数据类型转换都有objectBean里面来处理，故可能有异常，需要进行相应的处理
             */
            e.printStackTrace();
            /**
             * 一般可能发生的异常
             */
            List exList = Arrays.asList("ParseException", "NumberFormatException");
            Throwable t = e.getCause();
            while (t != null) {
                String causeClazz = t.getClass().getSimpleName();
                if (exList.contains(causeClazz)) {
                    this.description = "第" + rowIndex + "行，类型转换失败：" + t.getMessage();
                    return false;
                } else if (Magic.VALIDATION_EXECEPTION.equals(causeClazz)) {
                    /**
                     * 自定义异常
                     */
                    this.description = "第" + rowIndex + "行," + t.getMessage();
                    return false;
                } else {
                    t = t.getCause();
                }
            }
            this.description = "数据错误";
            return false;
        }
        /**
         * 校验，校验规则是配置在objectBean对象里面
         */
        Set<ConstraintViolation<Object>> constraintViolations = validator.validate(objectBean);
        if (constraintViolations.size() > 0) {
            /**
             * 校验失败时，提示相应信息
             */
            this.description = "第" + rowIndex + "行，校验出错：";
            for (ConstraintViolation<Object> vl : constraintViolations) {
                this.description = this.description + vl.getMessage() + " ;  ";
            }
            return false;
        }
        return true;
    }

    private String[] getRowValues(Row row, int columnStartIndex) {
        String[] values = new String[fieldSize];
        for (int j = columnStartIndex, t = 0; t < fieldSize; j++, t++) {
            Cell c = row.getCell(j);
            if (c == null) {
                values[t] = "";
                continue;
            }
            switch (c.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    values[t] = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    values[t] = String.valueOf(c.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:

                    if (HSSFDateUtil.isCellDateFormatted(c)) {
                        values[t] = format.format(c.getDateCellValue());
                    } else if (c.getCellStyle().getDataFormat() == 58) {
                        /**
                         * 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                         */
                        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");
                        double value = c.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);

                        values[t] = sdf.format(date);
                    } else {
                        values[t] = new DecimalFormat("#").format(c.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    values[t] = String.valueOf(c.getStringCellValue());
                    break;

                default:
                    values[t] = "";
                    break;
            }
        }
        return values;
    }

    /**
     * 根据某一个单元格，得到更人性化的显示,例如“A4”
     *
     * @param cell
     * @return
     */
    private String getCellRef(Cell cell) {
        return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
    }

    private List<String> readFieldsFromXML(String xmlFilename) throws ValidationException {
        SAXReader reader = new SAXReader();
        Document document = null;

        try {
            document = reader.read(new File(xmlFilename));// 加载配置文件
        } catch (DocumentException e) {
            e.printStackTrace();
            this.description = "IO 异常，读取配置文件失败";
            throw new ValidationException(xmlFilename, "IO 异常，读取配置文件失败");
        }

        Element root = document.getRootElement();
        List<String> fields = new ArrayList<String>();
        for (Iterator iter = root.elementIterator("field"); iter.hasNext(); ) {
            Element field = (Element) iter.next();
            fields.add(field.getTextTrim());
        }

        return fields;
    }

    /**
     * 从execel表中找到数据开始的单元格
     *
     * @return
     */
    private Cell findStartCell() {
        String firstFieldDesc = this.fieldList.get(0);
        int endRow = sheet.getLastRowNum() > 100 ? 100 : sheet.getLastRowNum();
        for (int i = 0; i <= endRow; i++) {
            Row r = sheet.getRow(i);
            if (r == null) {
                continue;
            }
            for (int j = 0; j < r.getLastCellNum(); j++) {
                Cell c = r.getCell(j);
                if (c == null) {
                    continue;
                }
                if (c.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (c.getStringCellValue().trim().equals(firstFieldDesc)) {
                        /**
                         * 找到第一个符合要求的字段，接下来判断它相邻的字段是否都符合要求
                         */
                        if (fieldList.size() > r.getLastCellNum() - j) {
                            this.description = "execel表格与所给配置描述不符，请下载模板文件";
                            return null;
                        }
                        for (int k = j + 1, t = 1; k <= j + fieldList.size() - 1; k++, t++) {
                            Cell c2 = r.getCell(k);
                            if (c2 == null) {
                                this.description = "请确保单元格" + this.getCellRef(c2) + "内容是\"" + fieldList.get(t) + "\"";
                                return null;
                            }
                            if (c2.getCellType() == Cell.CELL_TYPE_STRING) {
                                if (c2.getStringCellValue().contains(fieldList.get(t))) {
                                    continue;
                                } else {
                                    this.description = "请确保单元格" + this.getCellRef(c2) + "内容是\"" + fieldList.get(t) + "\"";
                                    return null;
                                }
                            }

                        }
                        return c;
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
            }
        }
        this.description = "找不到\"" + fieldList.get(0) + "\"这一列";
        return null;
    }

    public int getRowIndex() {
        return rowIndex;
    }

    public DateFormat getFormat() {
        return format;
    }


    public String createExcelIncludeFailReason(InputStream execelIS, String path, String newExcelName, HashMap<Integer, String> errHash) {
        FileOutputStream file;
        try {
            file = new FileOutputStream(path + "/" + newExcelName);
            Workbook workbook = this.getWorkbook(execelIS, newExcelName);
            Sheet s = workbook.getSheetAt(0);
            int endRow = sheet.getLastRowNum();
            for (int i = 1; i <= endRow; i++) {
                if (errHash.get(i) != null) {
                    Row rowkk = s.getRow(i);
                    Cell errorCell = rowkk.createCell(fieldList.size());
                    errorCell.setCellValue(errHash.get(i));
                }
            }
            workbook.write(file);
            file.close();
        } catch (FileNotFoundException e) {

            e.printStackTrace();
        } catch (IOException e) {

            e.printStackTrace();
        } catch (ValidationException e) {

            e.printStackTrace();
        }

        return newExcelName;
    }


    public String getFile(InputStream execelIS, String path, String appPath, List<Integer> listF, boolean b) {
        FileOutputStream file;
        try {
            file = new FileOutputStream(path + "/" + appPath);
            Workbook workbook = this.getWorkbook(execelIS);
            Sheet s = workbook.getSheetAt(0);
            Row row = null;
            int endRow = sheet.getLastRowNum();
            for (int i = 1; i <= endRow; i++) {

                Row rowkk = s.getRow(i);
                Cell infoCell = rowkk.createCell(fieldList.size());
                infoCell.setCellValue("sss");

                /**
                 * 删除错误行
                 */
                if (b && listF.contains(i)) {
                    row = s.getRow(i);
                    if (row != null) {
                        s.removeRow(row);
                    }
                }
                /**
                 * 删除正确行
                 */
                if (!b && !listF.contains(i)) {
                    row = s.getRow(i);
                    if (row != null) {
                        s.removeRow(row);
                    }
                }
            }
            workbook.write(file);
            file.close();
        } catch (FileNotFoundException e) {

            e.printStackTrace();
        } catch (IOException e) {

            e.printStackTrace();
        } catch (ValidationException e) {

            e.printStackTrace();
        }

        return appPath;
    }

    public Workbook getWorkbook(InputStream execelIS, String fileName) throws ValidationException, IOException {
        Workbook wb = null;
        //区别excel2003和excel2007版本
        String type = fileName.substring(fileName.lastIndexOf("."));
        if (".xlsx".equals(type)) {
            wb = new XSSFWorkbook(execelIS);
        } else {
            wb = new HSSFWorkbook(execelIS);
        }
        return wb;
    }

    public Workbook getWorkbook(InputStream execelIS) throws ValidationException, IOException {
        Workbook wb = null;
        /**
         * 区别excel2003和excel2007版本
         */
        try {
            wb = new HSSFWorkbook(execelIS);
        } catch (Exception e) {
            wb = new XSSFWorkbook(execelIS);
        }
        return wb;
    }
}
